package com.export.service.dynamic;

import com.export.entity.ReportParam;
import org.springframework.stereotype.Component;

/**
 * 接口分类报表动态sql
 *
 * @author yuanqinglong
 * @since 2021/7/27 9:32
 */
@Component
public class ProductCategoryReportDynamicStatement {


    //**********************************接口分类报表*************************************//




    /**
     * 接口分类报告导出sql
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportExportSql(ReportParam reportParam) {
        return "SELECT\n" +
                "p.prod_type_fid,prty_fname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "LEFT JOIN sys.sys_prty sp ON sp.prty_fid = p.prod_type_fid" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate} GROUP BY p.prod_type_fid,sp.prty_fname";
    }




    /**
     * 接口分类报告详情导出
     *
     * @param reportParam 报告参数
     * @return {@link String}
     */
    public String productCategoryReportDetailExportSql(ReportParam reportParam) {
        String grouping = reportParam.statisticalTimeTypeGrouping();
        return "SELECT\n" +
                "TO_CHAR(stat_date, '" + grouping + "') AS stat_date,p.prod_type_fid,\n" +
                "pt.prty_fname,\n" +
                "SUM(stat_req_num) AS stat_req_num,\n" +
                "SUM(stat_findout_num) AS stat_findout_num,\n" +
                "SUM(stat_fail_num) AS stat_fail_num\n" +
                "FROM sys.sys_stat sta\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = sta.stat_prod_uid\n" +
                "LEFT JOIN sys.sys_prty pt ON pt.prty_fid = p.prod_type_fid" +
                " WHERE stat_date BETWEEN #{startDate} AND #{endDate} AND prod_type_fid = #{prodTypeFid}" +
                " GROUP BY p.prod_type_fid,pt.prty_fname, TO_CHAR(stat_date, '" + grouping + "')";
    }




}
